<?php

require_once LIBS_DIRECTORY.OS_PATH_SEPARATOR.'jglib'.OS_PATH_SEPARATOR.'quickLite.php';

/*
 * phplister specific database SQL bqsed on SQLite version 2
 */
class Database extends QuickLite
{
	// How many page
	public function getNumberOfPage($type, $meta)
	{
		if(is_null($type) && is_null($meta))
		{
			return 0;
		}
		
		if(!is_null($type))
		{
			$type = (int)$type;
			if(is_null($meta))
			{
				$query = 'SELECT value FROM id WHERE name="'.COUNTER_PREFIX.COUNTER_TYPE_NAME.$type.'" LIMIT 1;';
				$result = debug($this->arrayQuery($query), 'Number of page query result', JGLIB_DEBUG_SQLR);
				if(isset($result[0]['value']))
				{
					$n = $result[0]['value'];
				}
				else
				{
					$n = 0;
				}
			}
			else
			{
				$meta = (int)$meta;
				$query = 'SELECT counter FROM '.META_TABLE.' WHERE id='.$meta.' AND type='.$type.' LIMIT 1;';
				$result = debug($this->arrayQuery($query), 'Number of page query result', JGLIB_DEBUG_SQLR);
				if(isset($result[0]['counter']))
				{
					$n = $result[0]['counter'];
				}
				else
				{
					$n = 0;
				}
			}
			
			return ceil($n / ITEM_PER_PAGE);
		}
	}
	
	// Get items
	public function getItems($type = null, $meta = null, $page = null)
	{
		$where = $orderBy = $limit = $join = '';
		
		if(!is_null($type))
		{
			$where .= ' AND i.type='.$type.' ';
		}
		
		if(!is_null($meta))
		{
			$join = 'JOIN '.ITEM_META_TABLE.' im ON im.id_item = i.id';
			$where .= ' AND im.id_meta='.$meta.' ';
		}
		
		$orderBy = 'ORDER BY i.id DESC';
		
		$from = 0;
		if(!is_null($page))
		{
			$from = ($page-1)*ITEM_PER_PAGE;
		}
		
		$limit = 'LIMIT '.$from.', '.ITEM_PER_PAGE;
		
		if(!empty($where))
		{
			$where = 'WHERE 1=1 '.$where;
		}
		
		$query = 'SELECT i.id as id , i.name as name , i.uri as uri , i.type as type , i.user as user ,
i.right as right , i.creation_time as creation_time, i.hash as hash FROM '.ITEM_TABLE.' i '.$join.' '.$where.' '.$orderBy.' '.$limit.';';
		return $this->arrayQuery(debug($query, 'SQL', JGLIB_DEBUG_SQLQ));
	}

	// Get tags
	public function getTags($type = null)
	{
		$where = $orderBy = $limit = '';
		
		if(is_null($type)) return array();
		
		$where = 'WHERE type='.$type;
		
		$orderBy = 'ORDER BY value ASC';
		
		$query = 'SELECT * FROM '.META_TABLE.' '.$where.' '.$orderBy.' '.$limit.';';
		return $this->arrayQuery(debug($query, 'SQL', JGLIB_DEBUG_SQLQ));
	}

	// Create/Empty tables for phplister3
	public function setup()
	{
		parent::setup();
		// The SQL IF EXISTS statement doesn't seem to work here
		// So we're using the @ :s
	
		// Drop first
		@$this->queryExec('DROP TABLE '.ITEM_TABLE.';');
		@$this->queryExec('DROP TABLE '.META_TABLE.';');
		@$this->queryExec('DROP TABLE '.ITEM_META_TABLE.';');

		$item = "
CREATE TABLE ".ITEM_TABLE." (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR NOT NULL,
	uri VARCHAR NOT NULL,
	type INT NOT NULL DEFAULT 1,
	user INT NOT NULL,
	right INT NOT NULL,
	creation_time DATETIME NOT NULL,
	hash VARCHAR NOT NULL
);";

		$meta = "
CREATE TABLE ".META_TABLE." ( 
	id INT NOT NULL, 
	value VARCHAR NOT NULL, 
	type INT NOT NULL DEFAULT 1, 
	counter INT NOT NULL 
);
CREATE UNIQUE INDEX id_type_primary ON meta(id,type);";

		$item_meta = "
CREATE TABLE ".ITEM_META_TABLE." ( 
	id_item INT NOT NULL, 
	id_meta INT NOT NULL 
);
CREATE UNIQUE INDEX meta_item_index ON ".ITEM_META_TABLE."(id_item,id_meta);";

		// Create then
		debug($this->queryExec($item), 'Create ITEM table...', JGLIB_DEBUG_SQLR);
		debug($this->queryExec($meta), 'Create META table...', JGLIB_DEBUG_SQLR);
		debug($this->queryExec($item_meta), 'Create ITEM_META table...', JGLIB_DEBUG_SQLR);
	}
}
